# -*- coding: UTF-8 -*-
from appraisals.consql import conn
import pandas as pd
import numpy as np
import re
from main import year,month


def Gzhm(x): #清洗故障号码数据
    a=[]
    for i in x:
        if i != 0 :
            a.append(re.sub(r'[^\x00-\x7f]','0',i))
        else:
            a.append(i)
    return a

def count_len(x): #统计故障号码长度
    a=[]
    for i in x:
        a.append(len(str(i)))
    return a

def RepCount(x): #重复次数
    y = np.array(x).tolist()  #转为字符串
    a =[]
    for i in y:
        a.append(y.count(i))
    return a

def Feedback(m,x): #判断跟踪
    a=[]
    for i,n  in zip(x,m):
        flag =0
        for j in list:
            if j in i and j !=n:
               flag = 1
               if flag != 0:
                   break
            else:
                flag = 0
        if flag !=0:
            a.append('跟踪')  #跟踪
        else:
            a.append('非跟踪')  #非跟踪
    return a

def Repetition(x,y):  #判断重复
    a = []
    n = len(x)
    print("n is {}".format(n))
    m =0
    while n -m >0:
        i = x[m]
        list =np.array(y[m:m+i]).tolist()
        if "跟踪" in list:
            k = 1
            for L in list:
                if L !="跟踪":
                    a.append("重复")
                    k +=1
            while i-k > 1:
                k += 1
                a.append("非重复")
        else:
            while i > 0:
                i -= 1
                a.append('重复')
            a.append('非重复')
        m += i
    print("a is {}".format(len(a)))
    return a

def Ptime(t,x):  #计算时间差
    a = []
    m = len(x)
    for i in range(m-1):
        if t[i] !=t[i+1]:
            a.append(0)
        else:
            a.append((x[i+1]-x[i]).days)
    a.append(0)
    return a

def PDrp(x,y,z):  #最后判断重复
    a=[]
    n =0
    m = len(x)
    while m-n > 0:
        if x[n] == '跟踪':
            a.append("不重复")
        elif y[n] != 0 or z[n] !='重复':
            a.append("不重复")
        else:
            a.append("重复")
        n +=1
    return a

list=['颜建亮','陈凯','李宇','刘胜斌','陈伟','潘朝蕊'] #判断规则
sql_zwyczc ="select * from zwyczc where year(CreateTime)="+year+" and month(CreateTime)="+month+";" #装维远程支撑统计
df = pd.read_sql(sql_zwyczc,con=conn)
df_sum = df.groupby('create_user',as_index=False)['TableID'].count()
df_sum.columns=['记录人','电话总量'] #sum
df_sum['电话总量']=df_sum['电话总量'].fillna(value=0)

df['gzhm'] =df['gzhm'].fillna(value=0)
df['gzhm'] =Gzhm(df['gzhm'])
df["len"]=count_len(df['gzhm'])
df['orfeedback'] = Feedback(df['create_user'],df['feedback']) #判断跟踪
df_gz =df.loc[df['orfeedback'] =='跟踪']
df_gz =df_gz.groupby('create_user',as_index=False)['TableID'].count()
df_gz.columns =['记录人','跟踪数'] # 统计跟踪数据  #gz
df_gz['跟踪数']=df_gz['跟踪数'].fillna(value=0)

df_wuxiao = df.loc[(df["len"]<8) & (df['orfeedback'] !="跟踪"),['TableID','create_user','orfeedback']] #无效数据
df_wuxiao =df_wuxiao.groupby('create_user',as_index=False)['TableID'].count().fillna(value=0)
df_wuxiao.columns=['记录人','登记不规范量']      #无效数据统计 #wx
df_wuxiao['登记不规范量']=df_wuxiao['登记不规范量'].fillna(value=0)

df_youxiao = df[['TableID','gzhm','CreateTime','create_user','feedback','orfeedback']].loc[(df["len"]>=8) &( (df['orfeedback'] =='跟踪') |(df['orfeedback'] =='非跟踪'))]  #有效数据
df_youxiao = df_youxiao.sort_values(by=['gzhm','CreateTime'],ascending=False).reset_index(drop=True) #排序并重新索引
df_youxiao['故障次数'] = RepCount(df_youxiao['gzhm'])
df_notrp = df_youxiao.loc[(df_youxiao['故障次数'] ==1) & (df_youxiao['orfeedback'] =='非跟踪')] #一次故障数据
df_notrp=df_notrp.groupby('create_user',as_index=False)['TableID'].count()  #一次故障数据统计
df_notrp.columns=['记录人','一次非跟踪量']  #重命名列

df_rp = df_youxiao.loc[df_youxiao['故障次数'] !=1] #重复数据,，重新索引
df_rp = df_rp.sort_values(by=['gzhm','TableID'],ascending=True).reset_index(drop=True)

Repetition(df_rp['故障次数'],df_rp['orfeedback'])
# df_rp["是否重复反馈"]=Repetition(df_rp['故障次数'],df_rp['orfeedback'])
# print(df_rp["是否重复反馈"])
# df_rp["时间差"]=Ptime(df_rp['gzhm'],df_rp['CreateTime']) #计算时间差
# df_rp["重复判定"]=PDrp(df_rp['orfeedback'],df_rp['时间差'],df_rp['是否重复反馈'])  #最后判断重复
# df_rp_count =df_rp.loc[df_rp["重复判定"] =='重复']  #筛选出重复数据
# df_rp_count =df_rp_count.groupby('create_user',as_index=False)['TableID'].count() #重复报表
# df_rp_count.columns=['记录人','重复量']    #rp
# df_rp_count['重复量']=df_rp_count['重复量'].fillna(value=0)
# df_list =[df_wuxiao,df_gz,df_rp_count] #合并表格
#
# for df_one in df_list:
#     df_sum =pd.merge(df_sum,df_one,how='outer')
# df_sum['电话量'] =(df_sum['电话总量']-df_sum['登记不规范量']-df_sum['跟踪数']-df_sum['重复量'])
# df_sum =df_sum.sort_values(by=['记录人'])
# phone_sum =df_sum['电话量'].sum()
# df_rp.to_excel('aa.xlsx',index=False)
# df_rp.to_excel('aaa.xlsx')
# print('合计用时：{}'.format(time.time()-start))
